package com.user.dao.impl;

import com.user.dao.IUserdao;
import com.user.entity.BookInfo;
import com.user.entity.Users;
import com.user.util.DBUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserdaoImpl implements IUserdao {
    @Override

    //用户登录
    public boolean userLogin(Users users) {

        ResultSet resultSet = null;
        boolean result = false;

        String sql = "select count(*) from users" +
                " where username =? and password=?";

        Object[] params = {users.getUsername(), users.getPassword()};
        try {
            resultSet = DBUtil.executeQuery(sql, params);
            if (resultSet.next()) {
                if (resultSet.getInt(1) != 0) {
                    result = true;
                } else
                    result = false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);
        return result;
    }

    //用户注册
    @Override
    public boolean userRegister(Users users) {
        boolean result = false;
        String sql = "insert into users(id,username,password,schoolid) values(seq_userid.nextval,?,?,?) ";
        Object[] params = {users.getUsername(), users.getPassword(), users.getShoolid()};
        try {
            result = DBUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return result;
    }

    //用户名是否存在
    @Override
    public boolean queryUsernameIsExist(Users users) {
        ResultSet resultSet = null;
        boolean result = false;
        String sql = "select count(1) from users where username =?";
        Object[] params = {users.getUsername()};
        try {
            resultSet = DBUtil.executeQuery(sql, params);
            if (resultSet.next()) {
                if (resultSet.getInt(1) != 0) {
                    result = true;
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);
        return result;
    }

    //学校号是否存在
    @Override
    public boolean querySchoolidIsExist(Users users) {
        ResultSet resultSet = null;
        boolean result = false;
        String sql = "select count(1) from school where schoolid =?";
        Object[] params = {users.getShoolid()};
        try {
            resultSet = DBUtil.executeQuery(sql, params);
            if (resultSet.next()) {
                if (resultSet.getInt(1) != 0) {
                    result = true;
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);
        return result;
    }

    //所有的书籍
    @Override
    public List<BookInfo> bookList() {
        List<BookInfo> bookList = new ArrayList<>();
        ResultSet resultSet = null;
        String sql = "select * from bookinfo";
        Object[] params = null;

        try {
            resultSet = DBUtil.executeQuery(sql, params);
            while (resultSet.next()) {
                int bookid = resultSet.getInt("bookid");
                String bookname = resultSet.getString("bookname");
                String author = resultSet.getString("author");
                String category = resultSet.getString("category");
                int number = resultSet.getInt("number");
                bookList.add(new BookInfo(bookid, bookname, author, category, number));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);
        return bookList;
    }

    @Override
    //分页
    public List<BookInfo> queryBookInfoByPage(int currentPage, int pageSize) {
        ResultSet resultSet = null;
        List<BookInfo> bookList = new ArrayList<>();
        String sql = "select *from "
                + "("
                + "select rownum r, t.* from"
                + "(select s.* from BookInfo s order by bookid asc) t "

                + "where rownum<=?"
                + ")"
                + "where r>=?";
        Object[] params = {pageSize * currentPage, pageSize * currentPage - pageSize + 1};
        try {
            resultSet = DBUtil.executeQuery(sql, params);
            while (resultSet.next()) {
                int bookid = resultSet.getInt("bookid");
                String bookname = resultSet.getString("bookname");
                String author = resultSet.getString("author");
                String category = resultSet.getString("category");
                int number = resultSet.getInt("number");
                bookList.add(new BookInfo(bookid, bookname, author, category, number));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);
        return bookList;

    }

    @Override
    public int getBookInfoTotalCount() {
        int totalCount = 0;
        String sql = "select count(*) from bookInfo ";
        try {
            totalCount = DBUtil.getTotalCount(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return totalCount;
    }

    @Override
    //借阅总数
    public int getBorrowBooksTotalCount(String username) {
        int totalCount = 0;
        String sql = "SELECT count(*)" +
                " FROM USERS,BOOKINFO,USERBOOKS" +
                " WHERE USERBOOKS.ID =users.ID AND" +
                " BOOKINFO.BOOKID = USERBOOKS.BOOKID" +
                " and users.username = '" + username + "'";
        try {
            totalCount = DBUtil.getTotalCount(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return totalCount;
    }

    @Override
    public int getIdByUsername(String username) {
        int id = -1;
        ResultSet resultSet = null;
        String sql = "select id from users where username =?";
        Object[] params = {username};
        try {
            resultSet = DBUtil.executeQuery(sql, params);
            if (resultSet.next()) {
                id = resultSet.getInt("id");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);
        return id;
    }

    @Override
    public String getPasswordByUsername(String username) {
        ResultSet resultSet = null;
        String password = null;
        String sql = "select password from users where username=?";
        Object[] params = {username};
        try {
            resultSet = DBUtil.executeQuery(sql, params);
            if (resultSet.next()) {
                password = resultSet.getString("password");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);
        return password;
    }

    @Override
    //借阅书籍--删除了书籍列表的数据
    public boolean borrowBooks(Users users, BookInfo bookInfo) {
        boolean result = false;
        String sql = "delete from userbooks where id = (select id from users where username =?) and bookid =?";
        Object[] params = {users.getUsername(), bookInfo.getBookid()};
        try {
            result = DBUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }


    @Override
    //为userbooks表添加数据
    public boolean UserBorrowBooks(int id, BookInfo bookInfo) {
        boolean result = false;
        String sql = "insert into userbooks (id,bookid) values(?,?)";
        Object[] params = {id, bookInfo.getBookid()};
        try {
            result = DBUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    //还书
    public boolean returnBooks(Users users, BookInfo bookInfo) {
        boolean result = false;
        String sql = "delete from userbooks where userbooks.id = (SELECT ID FROM USERS WHERE USERNAME =?)  and bookid =?";
        Object[] params = {users.getUsername(), bookInfo.getBookid()};
        try {
            result = DBUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return result;
    }


    @Override
    //增加书籍
    public boolean addBook(BookInfo bookInfo) {
        boolean result = false;
        String sql = "update bookinfo set BOOKINFO.\"number\"= BOOKINFO.\"number\"+1 where bookid =?";
        Object[] params = {bookInfo.getBookid()};
        try {
            result = DBUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    //减少书籍
    public boolean reduceBook(BookInfo bookInfo) {
        boolean result = false;
        String sql = "update bookinfo set BOOKINFO.\"number\"= BOOKINFO.\"number\"-1 where bookid =?";
        Object[] params = {bookInfo.getBookid()};
        try {
            result = DBUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }


    @Override
    //借阅书籍的信息
    public List<BookInfo> usersBooks(Users users) {
        List<BookInfo> bookList = new ArrayList<>();
        ResultSet resultSet = null;
        String sql = "SELECT BOOKINFO.* FROM USERS,BOOKINFO,USERBOOKS WHERE USERBOOKS.ID =users.ID AND BOOKINFO.BOOKID = USERBOOKS.BOOKID and users.username =?";
        Object[] params = {users.getUsername()};
        try {
            resultSet = DBUtil.executeQuery(sql, params);
            while (resultSet.next()) {
                int bookid = resultSet.getInt("bookid");
                String bookname = resultSet.getString("bookname");
                String author = resultSet.getString("author");
                String category = resultSet.getString("category");
                int number = resultSet.getInt("number");
                bookList.add(new BookInfo(bookid, bookname, author, category, number));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);
        return bookList;
    }

    @Override
    public List<BookInfo> queryBooksByCondition(BookInfo bookInfo) {
        List<BookInfo> bookList = new ArrayList<>();
        ResultSet resultSet = null;
        Object[] params = null;
        System.out.println(bookInfo);
        String sql = "select * from bookinfo where bookname like ? and category =? and author like ?";
        if ("".equals(bookInfo.getBookname())) {
            params = new Object[]{"%", bookInfo.getCategory(), "%" + bookInfo.getAuthor() + "%"};
        } else if ("".equals(bookInfo.getAuthor())) {
            params = new Object[]{"%" + bookInfo.getBookname() + "%", bookInfo.getCategory(), "%"};
        } else {
            params = new Object[]{"%" + bookInfo.getBookname() + "%", bookInfo.getCategory(), "%" + bookInfo.getAuthor() + "%"};
        }
        try {
            resultSet = DBUtil.executeQuery(sql, params);
            while (resultSet.next()) {
                int bookid = resultSet.getInt("bookid");
                String bookname = resultSet.getString("bookname");
                String author = resultSet.getString("author");
                String category = resultSet.getString("category");
                int number = resultSet.getInt("number");
                if (number != 0)
                    bookList.add(new BookInfo(bookid, bookname, author, category, number));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);
        return bookList;
    }

    @Override
    public boolean changeUserPassword(Users users) {
        boolean result = false;
        String sql = "update users set password =? where username =?";
        Object[] params = {users.getPassword(), users.getUsername()};
        try {
            result = DBUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;

    }
}
